import joblib
import pandas as pd
import streamlit as st
import matplotlib.pyplot as plt
import seaborn as sns
from matplotlib import rcParams
import plotly.express as px

from sql_helper import helper, UserService

# ========================= Streamlit 页面配置 =========================
st.set_page_config(page_title="房价预测系统", layout="wide", page_icon="🏡")

# 中文字体设置
rcParams['font.sans-serif'] = ['SimHei']
rcParams['axes.unicode_minus'] = False


# ========================= 缓存模型和数据库 =========================
@st.cache_resource
def load_rf_model(path="line.pkl"):
    return joblib.load(path)


@st.cache_resource
def load_rf1_model(path="rf.pkl"):
    return joblib.load(path)


@st.cache_resource
def get_db_connection():
    return helper(
        host="localhost",
        user="root",
        password="ak47qbz95",
        database="hqyj",
        port=3306,
        charset="utf8mb4"
    )


rf = load_rf_model()
rf1 = load_rf1_model()
db = get_db_connection()


# ========================= 历史记录页面 =========================
def history_page(db, username):
    st.header("📜 历史记录")

    # 查询用户 ID
    sql = "SELECT id FROM h_users WHERE username = %s"
    user_id_row = db.fetchone(sql, (username,))
    if not user_id_row:
        st.warning("⚠️ 用户不存在")
        return
    user_id = user_id_row[0]

    # 查询 records
    sql = """
    SELECT id, Predict, OverallQual, GrLivArea, FirstFlrSF, TotRmsAbvGrd,
           FullBath, YearBuilt, GarageCars, LotArea, created_at
    FROM records
    WHERE user_id = %s
    ORDER BY created_at DESC
    """
    records = db.fetchall(sql, (user_id,))
    if not records:
        st.info("暂无历史记录")
        return

    df = pd.DataFrame(records, columns=[
        "记录ID", "预测价格", "房屋质量", "地上居住面积", "一层面积",
        "地上房间总数", "完整浴室数量", "建造年份", "车库车位数",
        "地块面积", "保存时间"
    ])

    # 折线图
    st.subheader("📈 预测价格趋势")
    fig = px.line(df, x="保存时间", y="预测价格", markers=True)
    st.plotly_chart(fig, use_container_width=True)

    # 历史表格 + 删除
    st.subheader("📂 历史记录表")
    for i, row in df.iterrows():
        with st.expander(f"记录 {row['记录ID']} - {row['保存时间']}"):
            st.write(row.to_dict())
            if st.button(f"🗑 删除记录 {row['记录ID']}", key=f"del_{row['记录ID']}"):
                db.execute("DELETE FROM records WHERE id = %s AND user_id = %s", (row['记录ID'], user_id))
                st.success(f"记录 {row['记录ID']} 已删除")
                st.rerun()

    # 导出 CSV
    csv = df.to_csv(index=False).encode("utf-8-sig")
    st.download_button("📥 导出历史记录 (CSV)", csv, "history.csv", "text/csv")


# ========================= 保存数据函数 =========================
def save_data(db, username, OverallQual, GrLivArea, FirstFlrSF, TotRmsAbvGrd, FullBath,
              YearBuilt, GarageCars, LotArea, SalePrice):
    sql = "SELECT id FROM h_users WHERE username = %s"
    user_id_row = db.fetchone(sql, (username,))
    if not user_id_row:
        st.error("❌ 用户不存在，保存失败")
        return
    user_id = user_id_row[0]

    sql = """
    INSERT INTO records 
    (user_id, OverallQual, GrLivArea, FirstFlrSF, TotRmsAbvGrd, FullBath,
     YearBuilt, GarageCars, LotArea, Predict)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    params = (user_id, OverallQual, GrLivArea, FirstFlrSF, TotRmsAbvGrd, FullBath,
              YearBuilt, GarageCars, LotArea, SalePrice)
    db.execute(sql, params)


# ========================= 登录状态控制 =========================
if "logged_in" not in st.session_state:
    st.session_state.logged_in = False
if "username" not in st.session_state:
    st.session_state.username = ""
if "prediction" not in st.session_state:
    st.session_state.prediction = None

# ========================= 登录/注册界面 =========================
if not st.session_state.logged_in:
    st.title("🏡 房价预测系统登录")

    name = st.text_input("用户名", placeholder="请输入用户名")
    pwd = st.text_input("密码", placeholder="请输入密码", type="password")
    person = UserService(name, pwd)

    col1, col2 = st.columns(2)
    with col1:
        if st.button("登录"):
            if person.login(name, pwd):
                st.session_state.logged_in = True
                st.session_state.username = name
                st.rerun()
            else:
                st.error("❌ 用户名或密码错误")
    with col2:
        if st.button("注册"):
            if person.register(name, pwd):
                st.success("✅ 注册成功，请登录")
            else:
                st.error("⚠️ 用户名已存在")

# ========================= 登录后页面 =========================
else:
    st.sidebar.title(f"👋 欢迎 {st.session_state.username}")
    page = st.sidebar.radio("功能选择", ["房价预测", "数据分析", "历史记录"])
    if st.sidebar.button("退出登录"):
        st.session_state.update({"logged_in": False, "username": "", "prediction": None})
        st.rerun()

    # ------------------------- 房价预测 -------------------------
    if page == "房价预测":
        st.header("🏠 房屋信息输入")
        with st.form("prediction_form"):
            col1, col2 = st.columns(2)
            with col1:
                LotArea = st.number_input("地块面积 (平方英尺)", 1000, 200000, 2000)
                GrLivArea = st.number_input("地上居住面积 (平方英尺)", 100, 10000, 1500)
                FirstFlrSF = st.number_input("一层面积 (平方英尺)", 100, 5000, 1200)
                GarageCars = st.number_input("车库车位数", 0, 5, 2)
            with col2:
                TotRmsAbvGrd = st.number_input("地上房间总数", 1, 20, 6)
                FullBath = st.number_input("完整浴室数量", 0, 10, 2)
                YearBuilt = st.number_input("建造年份", 1800, 2025, 2000)
            OverallQual = st.slider("房屋整体质量评分 (0-10)", 0, 10, 5)
            submitted = st.form_submit_button("预测房价")

            save_clicked = st.form_submit_button("保存数据", disabled=not submitted)

        if submitted:

            # 构建输入数据
            input_data = pd.DataFrame([{
                "OverallQual": OverallQual,
                "GrLivArea": GrLivArea,
                "1stFlrSF": FirstFlrSF,
                "TotRmsAbvGrd": TotRmsAbvGrd,
                "FullBath": FullBath,
                "YearBuilt": YearBuilt,
                "GarageCars": GarageCars,
                "LotArea": LotArea,
            }])

            # 模型预测
            prediction = rf.predict(input_data)[0]
            st.session_state.prediction = prediction
            st.success(f"🏡 预测房价: ${prediction:,.0f}", icon="💰")

            # ---------------- 与预测房价最接近的 5 条数据 ----------------
            query = """
            SELECT OverallQual, GrLivArea, `1stFlrSF`, TotRmsAbvGrd, FullBath,
                   YearBuilt, GarageCars, LotArea, SalePrice
            FROM house_data
            ORDER BY ABS(SalePrice - %s)
            LIMIT 5
            """
            similar_houses = db.fetchall(query, (prediction,))
            similar_houses = pd.DataFrame(similar_houses, columns=[
                "房屋质量", "地上居住面积", "一层面积", "地上房间总数", "完整浴室数量",
                "建造年份", "车库车位数", "地块面积", "房价"
            ])

            st.markdown("### 🏘️ 与预测房价最接近的房屋")
            st.dataframe(similar_houses.style.format({"房价": "${:,.0f}"}), height=250)


        if save_clicked and st.session_state.prediction is not None:
            save_data(db, st.session_state.username, OverallQual, GrLivArea, FirstFlrSF,
                      TotRmsAbvGrd, FullBath, YearBuilt, GarageCars, LotArea, st.session_state.prediction)
            st.success("✅ 保存数据成功")

    # ------------------------- 数据分析 -------------------------
    elif page == "数据分析":
        st.header("📊 房屋数据分析")


        @st.cache_data(ttl=600)
        def load_data():
            query = """
            SELECT OverallQual, GrLivArea, `1stFlrSF`, TotRmsAbvGrd, FullBath, 
                   YearBuilt, GarageCars, LotArea, SalePrice
            FROM house_data
            """
            df = db.fetchall(query)
            return pd.DataFrame(df, columns=[
                "房屋质量", "地上居住面积", "一层面积", "地上房间总数", "完整浴室数量",
                "建造年份", "车库车位数", "地块面积", "房价"
            ])


        data = load_data()


        @st.cache_data
        def draw_price_distribution(df):
            fig, ax = plt.subplots(figsize=(10, 4))
            sns.histplot(df["房价"], bins=50, kde=True, color='skyblue', ax=ax)
            ax.set_title("房价分布直方图")
            ax.set_xlabel("房价")
            ax.set_ylabel("数量")
            return fig


        st.subheader("🏠 房价分布")
        st.pyplot(draw_price_distribution(data))

        st.subheader("📌 特征重要性")
        importances = rf1.feature_importances_
        feature_names = ["房屋质量", "地上居住面积", "一层面积", "地上房间总数",
                         "完整浴室数量", "建造年份", "车库车位数", "地块面积"]
        feat_df = pd.DataFrame({"特征": feature_names, "重要性": importances}).sort_values(by="重要性", ascending=False)


        @st.cache_data
        def draw_feature_importance(df):
            fig, ax = plt.subplots(figsize=(10, 4))
            sns.barplot(x="重要性", y="特征", data=df, ax=ax, palette="Blues_d")
            ax.set_title("特征重要性排序")
            return fig


        st.pyplot(draw_feature_importance(feat_df))

    # ------------------------- 历史记录 -------------------------
    elif page == "历史记录":
        history_page(db, st.session_state.username)
